const express = require('express');
const router = express.Router();
const db = require('../models/database');

// 获取所有库存信息
router.get('/', (req, res) => {
    const query = `
        SELECT p.id, p.name, p.category, i.quantity, i.location 
        FROM products p 
        LEFT JOIN inventory i ON p.id = i.product_id
    `;
    
    db.all(query, [], (err, rows) => {
        if (err) {
            return res.status(500).json({ error: err.message });
        }
        res.json(rows);
    });
});

// 入库操作
router.post('/in', (req, res) => {
    const { product_id, quantity, location, notes } = req.body;
    
    if (!product_id || !quantity || quantity <= 0) {
        return res.status(400).json({ error: 'Invalid input' });
    }
    
    // 开始事务
    db.serialize(() => {
        db.run('BEGIN TRANSACTION');
        
        // 检查库存记录是否存在
        db.get('SELECT * FROM inventory WHERE product_id = ?', [product_id], (err, row) => {
            if (err) {
                return db.run('ROLLBACK', () => {
                    res.status(500).json({ error: err.message });
                });
            }
            
            if (row) {
                // 更新现有库存
                const newQuantity = row.quantity + quantity;
                db.run(
                    'UPDATE inventory SET quantity = ?, location = ?, last_updated = CURRENT_TIMESTAMP WHERE product_id = ?',
                    [newQuantity, location || row.location, product_id],
                    function(err) {
                        if (err) {
                            return db.run('ROLLBACK', () => {
                                res.status(500).json({ error: err.message });
                            });
                        }
                        logInventoryOperation(product_id, quantity, 'in', notes, res);
                    }
                );
            } else {
                // 创建新库存记录
                db.run(
                    'INSERT INTO inventory (product_id, quantity, location) VALUES (?, ?, ?)',
                    [product_id, quantity, location],
                    function(err) {
                        if (err) {
                            return db.run('ROLLBACK', () => {
                                res.status(500).json({ error: err.message });
                            });
                        }
                        logInventoryOperation(product_id, quantity, 'in', notes, res);
                    }
                );
            }
        });
    });
});

// 出库操作
router.post('/out', (req, res) => {
    const { product_id, quantity, notes } = req.body;
    
    if (!product_id || !quantity || quantity <= 0) {
        return res.status(400).json({ error: 'Invalid input' });
    }
    
    // 开始事务
    db.serialize(() => {
        db.run('BEGIN TRANSACTION');
        
        // 检查库存是否足够
        db.get('SELECT * FROM inventory WHERE product_id = ?', [product_id], (err, row) => {
            if (err) {
                return db.run('ROLLBACK', () => {
                    res.status(500).json({ error: err.message });
                });
            }
            
            if (!row || row.quantity < quantity) {
                return db.run('ROLLBACK', () => {
                    res.status(400).json({ error: 'Insufficient inventory' });
                });
            }
            
            // 更新库存
            const newQuantity = row.quantity - quantity;
            db.run(
                'UPDATE inventory SET quantity = ?, last_updated = CURRENT_TIMESTAMP WHERE product_id = ?',
                [newQuantity, product_id],
                function(err) {
                    if (err) {
                        return db.run('ROLLBACK', () => {
                            res.status(500).json({ error: err.message });
                        });
                    }
                    logInventoryOperation(product_id, quantity, 'out', notes, res);
                }
            );
        });
    });
});

// 记录库存操作日志
function logInventoryOperation(product_id, quantity, operation_type, notes, res) {
    db.run(
        'INSERT INTO inventory_logs (product_id, quantity_change, operation_type, notes) VALUES (?, ?, ?, ?)',
        [product_id, quantity, operation_type, notes],
        function(err) {
            if (err) {
                return db.run('ROLLBACK', () => {
                    res.status(500).json({ error: err.message });
                });
            }
            db.run('COMMIT', (err) => {
                if (err) {
                    return res.status(500).json({ error: err.message });
                }
                res.json({ message: `Inventory ${operation_type} operation successful` });
            });
        }
    );
}

// 获取库存操作日志
router.get('/logs', (req, res) => {
    const query = `
        SELECT l.*, p.name as product_name 
        FROM inventory_logs l
        JOIN products p ON l.product_id = p.id
        ORDER BY l.operation_time DESC
    `;
    
    db.all(query, [], (err, rows) => {
        if (err) {
            return res.status(500).json({ error: err.message });
        }
        res.json(rows);
    });
});

module.exports = router;